package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBLayer.DbProduct.ProductContainer;
import ModelLayer.*;

public class DbInPartOrder {
	private  Connection con;
	private DbProduct dbProduct;

	public DbInPartOrder()
	{
		//Constructer wich initializes the connection object, and dbProduct object.
		con = DbConnection.getInstance().getDBcon();
		dbProduct = new DbProduct();
	}

	public InPartOrder findPartOrder(int partId) {
		//Finds a partOrder
		String wClause = "  partId = '" + partId + "'";
        return singleWhere(wClause);
	}
	
	public ArrayList<InPartOrder> findPartOrders(int orderId)
	{
		//Finds several partOrders using the orderid
		String wClause = "  orderId = '" + orderId + "'";
        return miscWhere(wClause);
		
	}
	
	public int insertPartOrder(InPartOrder pO, int orderId) throws Exception {
		//Inserts a partOrder-row by using the partOrder-object
		int nextPartId = GetMax.getMaxId("Select max(partId) from OutPartOrder");
        nextPartId = nextPartId + 1;
        System.out.println("next partId = " +  nextPartId);
  
       int rc = -1;
	   String queryPartOrder="INSERT INTO InPartOrder VALUES('"+
			   nextPartId  + "','"  +
			   orderId  + "','"  +
			   pO.getProductId()  + "','"  +
			   pO.getAmount()  + "')";
	   
	   try{
	    	  System.out.println("insert : " + queryPartOrder);
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(queryPartOrder);
	          stmt.close();
	      }
	       catch(SQLException ex){
	          System.out.println("InPartOrder is not inserted correct");
	          throw new Exception ("InPartOrder is not inserted correct");
	       }
      
       return(rc);
	}

	public int updatePartOrder(InPartOrder pOObj) {
		//Updates a partOrder-row by using the pOObj.
		InPartOrder pO = pOObj;
		int rc = -1;
		
		String query="UPDATE InPartOrder SET "+
			 	  "productId ='"+ pO.getProductId() + "', " +
	              "amount ='"+ pO.getAmount() + "', " +
			          " WHERE partId = '"+ pO.getPartId() + "'";
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	rc = stmt.executeUpdate(query);

		 	 	stmt.close();
			}
		 	catch(Exception ex){
		 	 	System.out.println("Update exception in outpartOrder db: "+ex);
		  	}
			return(rc);
	}

	public int delete(int partId) {
		//Delete a partOrder-row by using the partID.
		int rc=-1;
		  
	 	String queryPartOrder="DELETE FROM InPartOrder WHERE partId = '" +
				partId + "'";
                System.out.println(queryPartOrder);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(queryPartOrder);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in InPartOrder db: "+ex);
   	        }
	  	
		return(rc);
	}
	
	private InPartOrder singleWhere(String wClause)
	{
		//Returns a single partOrder
		ResultSet results;
		InPartOrder pO = new InPartOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
               pO = buildPartOrder(results);
                            
               stmt.close();
			}
            else{ 
               pO = null;
            }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return pO;
	}
	
	private ArrayList<InPartOrder> miscWhere(String wClause)
	{
		//Returns a several partOrders in a list
        ResultSet results;
	    ArrayList<InPartOrder> list = new ArrayList<InPartOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try{
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 InPartOrder pO = new InPartOrder();
	     	 pO = buildPartOrder(results);	
             list.add(pO);	
		}
                 stmt.close();		
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}	
	
	private String buildQuery(String wClause)
	{
		//Creates a query using the wClause-String
		String query="SELECT partId, orderId, productId, amount FROM InPartOrder";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private InPartOrder buildPartOrder(ResultSet results)
    {   
		//Builds a partOrder, using the resultSet
		InPartOrder pO = new InPartOrder();
        try{
        	pO.setPartId(results.getString("partId"));
        	pO.setAmount(Integer.parseInt(results.getString("amount")));
        	pO.setP(dbProduct.getProduct1(results.getString("productId")));
        }
       catch(Exception e)
       {
           System.out.println("error in building the inPartOrder object");
       }
       return pO;
    }
	
	
	
	public ArrayList<InPartOrder>  getAllPartOrders(String orderId) {
		//get all partorders in an arrayList
		ResultSet results;
	    ArrayList<InPartOrder> list = new ArrayList<InPartOrder>();	
		
	    String query =  "SELECT partId, productId, amount FROM InPartOrder";
	    query=query+" WHERE orderId = "+ orderId;
	    System.out.println(query);
	    
  
        try {
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
	
			while(results.next()) {
				InPartOrder obj;
		     	 obj = buildPartOrder(results);	
                 list.add(obj);	
			}
			stmt.close();
		}
	 	catch(Exception e) {
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
}